package Bank;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 连接数据库类
 * 
 * 封装简单的sql语句，方便查询
 * @author Intel
 *
 */
public class ConnDB {
	/**
	 * 获取连接数据库的connection
	 * @return connection
	 */
//	public Connection getConnection(){
//		try{
//			Class.forName("oracle.jdbc.driver.OracleDriver");
//			String url = "jdbc:oracle:thin:@localhost:1521:xe";
//			String user = "hr";
//			String password = "123456";
//			Connection conn = DriverManager.getConnection(url,user,password);
//		}catch(Exception e){
//			e.printStackTrace();
//		}
//		return conn;
//	}
	
//	static void open(){
//		try {
//			Class.forName("oracle.jdbc.OracleDriver");
//		} catch (ClassNotFoundException e) {
//			e.printStackTrace();
//		}
//		
//		try {
//			con = DriverManager.getConnection(
//					"jdbc:oracle:thin:@localhost:1521:XE", "hr", "123456");
//			stmt=con.createStatement();
//			
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
//	}
	
	/**
	 * 运行sql语句的方法
	 * 
	 * @param s：sql语句
	 * @return 结果集
	 */
	public ResultSet getrs(String s){
		//Connection conn = new ConnDB().getConnection();
//		try {
//			stmt = con.createStatement();
////			stmt = conn.createStatement();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
		ResultSet rs = null;
		DBOC.open();
		try {
			//System.out.println(s);
			//Statement stmt = DBOC.con.createStatement();
			rs = DBOC.stmt.executeQuery(s);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 执行更新语句
	 * @param sql语句
	 * @return 受影响的记录数
	 */
	public int uprs(String s){
		//Connection conn = new ConnDB().getConnection();
		//ResultSet rs = null;
		DBOC.open();
		int row = 0;
		//Statement stmt = null;
//		try {
//			//stmt = DBOC.con.createStatement();
//			DBOC.stmt
//		} catch (SQLException e2) {
//			// TODO Auto-generated catch block
//			e2.printStackTrace();
//		}
//		try {
////			stmt = conn1.createStatement();
//			stmt = con.createStatement();
//		} catch (SQLException e) {
//			e.printStackTrace();
//		}
		
		try {
			row = DBOC.stmt.executeUpdate(s);
//			conn1.commit();
			DBOC.con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			try {
//				conn1.close();
				DBOC.con.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
		}finally{
//			conn1 = null;
		}
		DBOC.close();
		return row;
	}
	
	/**
	 * 遍历id是否存在
	 * 
	 * @param：表明，id
	 * @return：
	 * -1：表名传入错误
	 * 0：id不存在
	 * 1：id存在
	 */
	public int idIsExist(String tName,long id){
		String tableId = null;
		int flag = 0;
		switch(tName){
		case "account_table" :tableId = "account_id";break;
		case "customer_table":tableId = "customer_id";break;
		case "employee_table":tableId = "employee_id";break;
		default:tableId = "wrong";
		}
		if(tableId == "wrong"){
			return -1;
		}else{
			String s = "select * from "+tName+" where "+ tableId +"="+id;
			ResultSet rs = new ConnDB().getrs(s);
			try {
				if(rs.next()){
					flag = 1;
				}else{
					flag = 0;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBOC.close();
			return flag;
		}
	}
	
	/**
	 * 遍历username是否存在
	 * 
	 * @param：表明，id
	 * @return：
	 * -1：表名传入错误
	 * 0：id不存在
	 * 1：id存在
	 */
	public int usernameIsExist(String tName,String name){
		String tableName = null;
		int flag = 0;
		switch(tName){
		case "customer_table":tableName = "customer_username";break;
		case "employee_table":tableName = "employee_name";break;
		default:tableName = "wrong";
		}
		if(tableName == "wrong"){
			return -1;
		}else{
			String s = "select * from "+tName+" where "+ tableName +"="+ "'"+name+"'";
			//System.out.println(s);
			ResultSet rs = new ConnDB().getrs(s);
			try {
				if(rs.next()){
					flag = 1;
				}else{
					flag = 0;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			DBOC.close();
			return flag;
		}
	}
	
	public void close(){
		DBOC.close();
	}
	
}


/**
 * DateBase Open & Close
 *
 */
class DBOC {
	static Connection con = null;
	static Statement stmt=null;
	static void open(){
		try {
			Class.forName("oracle.jdbc.OracleDriver");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		}
		
		try {
			con = DriverManager.getConnection(
					"jdbc:oracle:thin:@localhost:1521:XE", "hr", "123456");
			stmt=con.createStatement();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	static void close(){
		try {
			stmt.close();
			con.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	} 
}












